import re
from contextlib import contextmanager, suppress
import dmPython
# 达梦数据库没有constants模块,直接使用dmPython即可
from typing import TYPE_CHECKING, Any, Union
import frappe
from collections.abc import Iterable, Sequence
from frappe.database.database import Database, get_query_execution_timeout
from frappe.database.utils import EmptyQueryValues, LazyMogrify
from frappe.utils import cint, get_datetime, now, get_table_name
from frappe.query_builder.functions import Count
from frappe_dm.database.dm.schema import DmTable

class DmExceptionUtil:
    """达梦数据库异常处理工具类"""

    ProgrammingError = dmPython.DatabaseError
    TableMissingError = dmPython.DatabaseError
    OperationalError = dmPython.OperationalError 
    InternalError = dmPython.InternalError
    SQLError = dmPython.ProgrammingError
    DataError = dmPython.DataError
    InterfaceError = dmPython.InterfaceError
    SequenceGeneratorLimitExceeded = dmPython.DatabaseError

    @staticmethod
    def is_deadlocked(e):
        # 达梦死锁错误码
        return e.args[0].code == -6403

    @staticmethod
    def is_timedout(e):
        # 达梦超时错误码
        return e.args[0].code == -6407
        
    @staticmethod
    def is_read_only_mode_error(e) -> bool:
        return e.args[0].code == -6506
    
    @staticmethod
    def is_syntax_error(e):
        # 达梦语法错误码
        return e.args[0].code == -2007    
    
    @staticmethod
    def is_table_missing(e):
        # 达梦表不存在错误码
        return e.args[0].code == -2106

    @staticmethod
    def is_missing_column(e):
        # 达梦列不存在错误码 
        return e.args[0].code == -2115
        
    @staticmethod
    def is_duplicate_entry(e):
        # 达梦主键/唯一键冲突错误码
        return e.args[0].code == -6602
        
    @staticmethod
    def is_access_denied(e):
        # 达梦访问权限错误码
        return e.args[0].code == -5501
    
    @staticmethod
    def cant_drop_field_or_key(e):
        # 达梦删除字段或键约束错误码
        return e.args[0].code == -6602

    @staticmethod
    def is_duplicate_entry(e):
        # 达梦主键/唯一键冲突错误码
        return e.args[0].code == -6602

    @staticmethod
    def is_primary_key_violation(e):
        # 达梦主键冲突错误码
        return e.args[0].code == -6602

    @staticmethod
    def is_unique_key_violation(e):
        # 达梦唯一键冲突错误码
        return e.args[0].code == -6602

    @staticmethod
    def is_duplicate_fieldname(e):
        # 达梦重复字段名错误码
        return e.args[0].code == -2102

    @staticmethod
    def is_statement_timeout(e):
        # 达梦语句超时错误码
        return DmExceptionUtil.is_timedout(e) or isinstance(e, frappe.QueryTimeoutError)

    @staticmethod
    def is_data_too_long(e):
        # 达梦数据超长错误码
        return e.args[0].code == -6124
    
    @staticmethod
    def is_db_table_size_limit(e) -> bool:
        return False

    @staticmethod
    def is_interface_error(e):
        return isinstance(e, dmPython.InterfaceError)

class DmDatabase(DmExceptionUtil, Database):
    """达梦数据库实现类"""
    
    REGEX_CHARACTER = "REGEXP"
    default_port = "5236"
    
    def setup_type_map(self):
        """设置数据类型映射"""
        self.db_type = "dm"
        self.type_map = {
            'Currency': ('decimal', '21,9'),
            'Int': ('int', None),
            'Long Int': ('bigint', None),
            'Float': ('decimal', '21,9'),
            'Percent': ('decimal', '21,9'),
            'Check': ('int', None),
            'Small Text': ('text', ''),
            'Long Text': ('text', ''),
            'Code': ('text', ''),
            'Text Editor': ('text', ''),
            'Markdown Editor': ('text', ''),
            'HTML Editor': ('text', ''),
            'Date': ('date', ''),
            'Datetime': ('timestamp', '6'),
            'Time': ('time', '6'),
            'Text': ('text', ''),
            'Data': ('varchar', self.VARCHAR_LEN),
            'Link': ('varchar', self.VARCHAR_LEN),
            'Dynamic Link': ('varchar', self.VARCHAR_LEN),
            'Password': ('text', ''),
            'Select': ('varchar', self.VARCHAR_LEN),
            'Rating': ('decimal', '3,2'),
            'Read Only': ('varchar', self.VARCHAR_LEN),
            'Attach': ('text', ''),
            'Attach Image': ('text', ''),
            'Signature': ('text', ''),
            'Color': ('varchar', self.VARCHAR_LEN),
            'Barcode': ('text', ''),
            'Geolocation': ('text', ''),
            'Duration': ('decimal', '21,9'),
            'Icon': ('varchar', self.VARCHAR_LEN),
            'Phone': ('varchar', self.VARCHAR_LEN),
            'Autocomplete': ('varchar', self.VARCHAR_LEN),
            'JSON': ('text', '')
        }
    
    def connect(self):
        """连接到达梦数据库"""
        self.cur_db_name = self.user
        self._conn = self.get_connection()
        self._cursor = self._conn.cursor()

        try:
            if execution_timeout := get_query_execution_timeout():
                self.set_execution_timeout(execution_timeout)
        except Exception as e:
            self.logger.warning(f"Couldn't set execution timeout {e}")

    @property
    def last_query(self):
        """获取最后执行的SQL语句"""
        #print(self._cursor.statement)
        return self._cursor.statement


    def get_connection(self):
        """创建达梦数据库连接"""
        conn = dmPython.connect(
            user=self.user,
            password=self.password,
            host=self.host,
            port=int(self.port),
            autoCommit=False
        )
        
        return conn

    def set_execution_timeout(self, seconds: int):
        """设置查询超时时间"""
        self.sql("SET SESSION QUERY_TIMEOUT = %s", int(seconds) * 1000)

    def escape(self, s, percent=True):
        """转义特殊字符"""
        if isinstance(s, bytes):
            s = s.decode('utf-8')

        if s is None:
            s = ''

        if percent:
            s = s.replace("%", "%%")

        s = s.replace("'", "''")
        return f"'{s}'"

    def get_database_size(self):
        """获取数据库大小"""
        # 达梦特有的系统表查询
        sql = """
            SELECT SUM(BYTES)/1024/1024 as size 
            FROM V$TABLESPACE
        """
        res = self.sql(sql, as_dict=True)
        return res[0].get('size') if res else 0

    def sql(self, query, values=(), *args, **kwargs):
        """执行SQL语句"""
        if isinstance(query, str):
            # 修改查询语句以适配达梦语法
            query = self.modify_query(query)
            
            # 将 %(param)s 转换为 ? 占位符
            if values:
                query, values = self._convert_placeholders(query, values)
                    
        #print("Executing query:", query)  # 调试日志
        return super().sql(query, values, *args, **kwargs)

    def _convert_placeholders(self, query, values):
        """转换查询中的占位符"""
        if isinstance(values, dict):
            # 将字典参数转换为达梦按名称绑定字典的格式
            placeholders = []
            for key in sorted(values.keys()):
                placeholders.append(f":{key}")
            for key in placeholders:
                query = query.replace(f"%({key[1:]})s", key)
        else:
            # 元组/列表参数,直接替换 %s 为 ?
            query = query.replace('%s', '?')
        return query, values

    def lazy_mogrify(self, *args, **kwargs) -> str:
        return self.last_query

    def get_tables(self, cached=True):
        """获取所有表名"""
        return [d[0] for d in self.sql("""
            SELECT TABLE_NAME 
            FROM USER_TABLES 
            ORDER BY TABLE_NAME
        """)]


    @staticmethod
    def is_type_number(code):
        """判断是否为数字类型"""
        return code in (
            dmPython.DECIMAL,
            dmPython.TINY,
            dmPython.SHORT,
            dmPython.LONG,
            dmPython.FLOAT,
            dmPython.DOUBLE,
            dmPython.LONGLONG,
            dmPython.INT24,
            dmPython.YEAR,
            dmPython.NEWDECIMAL
        )

    @staticmethod
    def is_type_datetime(code):
        """判断是否为日期时间类型"""
        return code in (
            dmPython.TIMESTAMP,
            dmPython.DATE,
            dmPython.TIME,
            dmPython.DATETIME
        )
    
    def rename_table(self, old_name: str, new_name: str) -> list | tuple:
        """重命名表"""
        old_name = get_table_name(old_name)
        new_name = get_table_name(new_name)
        return self.sql_ddl(f'ALTER TABLE "{old_name}" RENAME TO "{new_name}"')

    def describe(self, doctype: str) -> list | tuple:
        """获取表结构"""
        table_name = get_table_name(doctype)
        return self.sql(f"""
            SELECT column_name 
            FROM all_tab_columns 
            WHERE table_name = '{table_name}'
            AND owner = UPPER('{self.user}')
            ORDER BY column_id
        """)

    def change_column_type(
        self, doctype: str, column: str, type: str, nullable: bool = False
    ) -> list | tuple:
        """修改列类型"""
        table_name = get_table_name(doctype)
        null_constraint = "NOT NULL" if not nullable else "NULL"
        return self.sql_ddl(f"""
            ALTER TABLE "{table_name}" 
            MODIFY "{column}" {type} {null_constraint}
        """)

    def rename_column(self, doctype: str, old_column_name: str, new_column_name: str):
        """重命名列"""
        table_name = get_table_name(doctype)
        self.sql_ddl(f"""
            ALTER TABLE "{table_name}" 
            RENAME COLUMN "{old_column_name}" TO "{new_column_name}"
        """)

    def create_auth_table(self):
        """创建认证表"""
        self.sql_ddl("""
            CREATE TABLE IF NOT EXISTS "__Auth" (
                "doctype" VARCHAR(140) NOT NULL,
                "name" VARCHAR(255) NOT NULL,
                "fieldname" VARCHAR(140) NOT NULL,
                "password" TEXT NOT NULL,
                "encrypted" INT NOT NULL DEFAULT 0,
                PRIMARY KEY ("doctype", "name", "fieldname")
            )
        """)


    def create_global_search_table(self):
        """创建全局搜索表"""
        if "__global_search" not in self.get_tables():
            self.sql(f"""
                CREATE TABLE "__global_search"(
                    doctype varchar(100),
                    name varchar({self.VARCHAR_LEN}),
                    title varchar({self.VARCHAR_LEN}),
                    content text,
                    route varchar({self.VARCHAR_LEN}),
                    published int default 0,
                    unique (doctype, name)
                )
            """)

    def create_user_settings_table(self):
        """创建用户设置表"""
        self.sql_ddl("""
            CREATE TABLE IF NOT EXISTS "__UserSettings" (
                "user" VARCHAR(180) NOT NULL,
                "doctype" VARCHAR(180) NOT NULL,
                "data" TEXT,
                CONSTRAINT "user_settings_pk" PRIMARY KEY ("user", "doctype")
            )
        """)

    def updatedb(self, doctype, meta=None):
        """
        同步DocType到数据库表
        - 如果需要则创建
        - 更新列
        - 更新索引
        """
        res = self.sql(f'select "issingle" from "tabDocType" where "name"=%s', (doctype,))
        if not res:
            raise Exception(f"Wrong doctype {doctype} in updatedb")

        if not res[0][0]:
            db_table = DmTable(doctype, meta)
            db_table.validate()
            db_table.sync()
            self.commit()
    @staticmethod
    def get_on_duplicate_update(key="name"):
        raise NotImplementedError("达梦数据库不支持 ON DUPLICATE UPDATE 语法")

    def has_index(self, table_name, index_name):
        """检查索引是否存在"""
        return bool(self.sql("""
            SELECT 1 FROM all_indexes 
            WHERE table_name=%s
            AND index_name=UPPER(%s)
            AND owner=UPPER(%s)
        """, (table_name, index_name, self.user)))

    def add_index(self, doctype: str, fields: list, index_name: str | None = None):
        """创建索引
        达梦数据库不支持在 BLOB/CLOB/TEXT 类型字段上创建普通索引
        """
        table_name = get_table_name(doctype)
        index_name = index_name or self.get_index_name(fields)
        
        if len(fields) == 1:
            meta = frappe.get_meta(doctype)
            field = fields[0]
            fields_str = re.sub(r"\(.*\)", "", field)
            df = meta.get_field(fields_str)
            if df and df.fieldtype == "Small Text":
                return self.sql_ddl(f'CREATE CONTEXT INDEX IF NOT EXISTS "{index_name}" ON "{table_name}" ("{fields_str}") LEXER DEFAULT_LEXER')
                
        fields_str = '", "'.join(re.sub(r"\(.*\)", "", field) for field in fields)
        self.sql_ddl(f'CREATE INDEX IF NOT EXISTS "{index_name}" ON "{table_name}" ("{fields_str}")')

    def add_unique(self, doctype: str, fields: str | list, constraint_name: str = None):
        """添加唯一约束"""
        if isinstance(fields, str):
            fields = [fields]
            
        if not constraint_name:
            constraint_name = "unique_" + "_".join(fields)

        if not self.sql("""
            SELECT constraint_name
            FROM all_constraints
            WHERE table_name = %s
            AND constraint_type = 'U'
            AND constraint_name = UPPER(%s)
            AND owner = UPPER(%s)
        """, ("tab" + doctype, constraint_name, self.user)):
            self.commit()
            fields_str = '", "'.join(fields)
            self.sql(f"""
                ALTER TABLE "tab{doctype}"
                ADD CONSTRAINT "{constraint_name}" UNIQUE ("{fields_str}")
            """)

    def get_table_columns_description(self, table_name):
        """获取表结构描述"""
        return self.sql("""
            WITH index_info AS (
                SELECT 
                    column_name,
                    MAX(CASE WHEN uniqueness = 'UNIQUE' THEN 1 ELSE 0 END) as is_unique,
                    MAX(CASE WHEN uniqueness = 'NONUNIQUE' THEN 1 ELSE 0 END) as is_index
                FROM all_ind_columns ic
                JOIN all_indexes i ON ic.index_name = i.index_name 
                    AND ic.table_name = i.table_name
                    AND ic.table_owner = i.owner
                WHERE ic.table_name = %s 
                AND ic.table_owner = UPPER(%s)
                GROUP BY column_name
            )
            SELECT 
                c.column_name as "name",
                CASE LOWER(c.data_type)
                    WHEN 'varchar2' THEN 'varchar(' || CAST(c.data_length AS VARCHAR) || ')'
                    WHEN 'timestamp(6)' THEN 'timestamp'
                    ELSE LOWER(c.data_type)
                END as "type",
                c.data_default as "default",
                COALESCE(i.is_index, 0) as "index",
                COALESCE(i.is_unique, 0) as "unique",
                c.nullable as "nullable",
                c.data_length as "data_length",
                c.data_precision as "data_precision",
                c.data_scale as "data_scale"
            FROM all_tab_columns c
            LEFT JOIN index_info i ON c.column_name = i.column_name
            WHERE c.table_name = %s
            AND c.owner = UPPER(%s)
            ORDER BY c.column_id
        """, (table_name, self.user, table_name, self.user), as_dict=1)

    def get_column_type(self, doctype: str, column: str) -> str:
        """获取列类型"""
        table_name = get_table_name(doctype)
        result = self.sql("""
            SELECT data_type 
            FROM all_tab_columns 
            WHERE table_name = %s
            AND column_name = %s
            AND owner = UPPER(%s)
        """, (table_name, column, self.user))
        return result[0][0] if result else ""

    def get_database_list(self):
        """获取所有数据库列表"""
        return self.sql("""
            SELECT username FROM all_users 
            WHERE username NOT IN ('SYS','SYSDBA','SYSAUDITOR','SYSSSO')
        """, pluck=True)

    def get_db_table_columns(self, table) -> list[str]:
        """返回指定表的列名列表"""
        columns = frappe.cache.hget("table_columns", table)
        if columns is None:
            # 使用达梦的系统视图查询列信息
            columns = self.sql("""
                SELECT column_name 
                FROM all_tab_columns 
                WHERE table_name = %s
                AND owner = UPPER(%s)
                ORDER BY column_id
            """, (table, self.user), pluck=True)

            if columns:
                frappe.cache.hset("table_columns", table, columns)

        return columns

    def get_column_index(self, table_name: str, fieldname: str, unique: bool = False) -> frappe._dict | None:
        """获取列的索引信息"""
        constraint_type = "U" if unique else "N" 
        result = self.sql(f"""
            SELECT 
                i.index_name as Key_name,
                i.uniqueness as Non_unique,
                ic.column_position as Seq_in_index
            FROM all_indexes i
            JOIN all_ind_columns ic ON i.index_name = ic.index_name 
            WHERE i.table_name = '{table_name}'
            AND ic.column_name = '{fieldname}'
            AND i.owner = UPPER('{self.user}')
            AND i.index_type = '{constraint_type}'
            AND ic.column_position = 1
        """, as_dict=True)
        
        return result[0] if result else None

    def get_row_size(self, doctype: str) -> int:
        """获取表的预估行大小(字节)"""
        table_name = get_table_name(doctype)
        result = self.sql(f"""
            SELECT SUM(
                CASE data_type
                    WHEN 'NUMBER' THEN 8
                    WHEN 'DATE' THEN 7
                    WHEN 'TIMESTAMP' THEN 11
                    WHEN 'CHAR' THEN data_length
                    WHEN 'VARCHAR' THEN data_length
                    WHEN 'TEXT' THEN 4000
                    ELSE 8
                END
            ) as row_size
            FROM all_tab_columns
            WHERE table_name = '{table_name}'
            AND owner = UPPER('{self.user}')
        """)
        return result[0][0] if result else 0

    @contextmanager
    def unbuffered_cursor(self):
        """创建无缓冲游标上下文"""
        try:
            if not self._conn:
                self.connect()
            
            original_cursor = self._cursor
            new_cursor = self._conn.cursor()
            new_cursor.arraysize = 1  # 设置为无缓冲模式
            self._cursor = new_cursor
            yield
        finally:
            self._cursor = original_cursor
            new_cursor.close()

    def connect(self):
        """连接到达梦数据库"""
        self.cur_db_name = self.user
        self._conn = self.get_connection()
        self._cursor = self._conn.cursor()

        try:
            if execution_timeout := get_query_execution_timeout():
                self.set_execution_timeout(execution_timeout)
        except Exception as e:
            self.logger.warning(f"无法设置执行超时: {e}")

    def get_connection_settings(self) -> dict:
        """获取连接设置"""
        return {
            "host": self.host,
            "user": self.user,
            "password": self.password,
            "port": int(self.port)
        }

    def is_primary_key_violation(self, e):
        """检查是否为主键冲突错误"""
        return self.is_duplicate_entry(e)

    def is_unique_key_violation(self, e):
        """检查是否为唯一键冲突错误"""
        return self.is_duplicate_entry(e)

    def is_duplicate_fieldname(self, e):
        """检查是否是重复字段名错误"""
        return "重复的列名" in str(e) or "duplicate column" in str(e).lower()

    def alter_table(self, doctype, commands):
        """分批执行 ALTER TABLE 命令"""
        table = get_table_name(doctype)
        
        # 达梦数据库要求每个 ALTER TABLE 语句只能包含一个修改操作
        for command in commands:
            try:
                if command.strip():
                    self.sql_ddl(f'ALTER TABLE "{table}" {command}')
            except Exception as e:
                if self.is_duplicate_fieldname(e):
                    continue
                else:
                    raise
    def get_creation_count(self, doctype, minutes):
        """Get count of records created in the last x minutes"""
        from dateutil.relativedelta import relativedelta
        from frappe.utils import now_datetime

        now = now_datetime()
        ago = now - relativedelta(minutes=minutes)

        if self.db_type == "dm":
            # 达梦数据库需要使用完整的时间戳格式
            creation_time = ago.strftime('%Y-%m-%d %H:%M:%S.%f')
            Table = frappe.qb.DocType(doctype)
            return (
                frappe.qb.from_(Table)
                .select(Count(Table.name))
                .where(Table.creation >= creation_time)
                .run()[0][0]
            )
        else:
            # 其他数据库调用父类方法
            return super().get_creation_count(doctype, minutes)

    def modify_query(self, query):
        """修改SQL语句以适配达梦语法
        1. 替换反引号为双引号
        2. 替换 LOCATE 为 INSTR
        3. 处理达梦保留字
        """
        # 替换反引号为双引号
        query = query.replace('`', '"')
        
        # 替换 LOCATE 为 INSTR
        query = re.sub(r'LOCATE\s*\((.*?),(.*?)\)', r'INSTR(\2,\1)', query, flags=re.IGNORECASE)
        
        # 处理达梦保留字
        # 在列名中将 user 替换为 "user"
        query = re.sub(r'(?i)(\s+)user(\s*=\s*)', r'\1"user"\2', query)
        query = re.sub(r'(?i)(\.)user(\s+|\)|,)', r'\1"user"\2', query)
        
        return query
        
    def bulk_insert(
        self,
        doctype: str,
        fields: list[str],
        values: Iterable[Sequence[Any]],
        ignore_duplicates=False,
        *,
        chunk_size=10_000,
    ):
        """
        达梦数据库批量插入实现
        
        Args:
            doctype: 文档类型名称
            fields: 要插入的字段列表
            values: 要插入的值序列
            ignore_duplicates: 是否忽略重复记录
            chunk_size: 每批次处理的记录数,默认10000
        """
        table = get_table_name(doctype)
        fields_str = ', '.join(f'"{f}"' for f in fields)
        
        def create_insert_sql(batch_size):
            """生成INSERT语句"""
            placeholders = ', '.join(['(' + ', '.join(['?'] * len(fields)) + ')'] * batch_size)
            return f'INSERT INTO "{table}" ({fields_str}) VALUES {placeholders}'
        
        def process_batch(batch_values):
            """处理一批数据"""
            if not batch_values:
                return
            
            flat_values = [v for row in batch_values for v in row]
            
            try:
                # 尝试批量插入
                self.sql(create_insert_sql(len(batch_values)), flat_values)
            except Exception as e:
                if ignore_duplicates:
                    # 如果需要忽略重复，则逐条尝试插入
                    for row in batch_values:
                        try:
                            single_sql = f'INSERT INTO "{table}" ({fields_str}) VALUES ({", ".join(["?"] * len(fields))})'
                            self.sql(single_sql, row)
                        except Exception as e2:
                            if not self.is_primary_key_violation(e2):
                                raise
                else:
                    raise
        
        # 分批处理数据
        batch = []
        for row in values:
            batch.append(row)
            if len(batch) >= chunk_size:
                process_batch(batch)
                batch = []
        
        # 处理最后一批数据
        if batch:
            process_batch(batch)